<?php

include_once APPLICATION_DIR . "/application/model/TECHNICIAN.php";

class Technician extends DataObjects_Technician
{
	public function validateInput(){
        global $error_msg;
        if($this->TITLE == ""){
            $error_msg->add(_MSG_TITLE_IS_EMPTY);
        }
		return $error_msg->isNoError();
    }
	public function canBeDeleted(){
		return true;
	}
	public static function getAvailableTech($date,$time,$listService){
		if($listService[strlen($listService) - 1] == ','){
			$listService = substr($listService, 0 , strlen($listService) - 1);
		}
		if($listService == "" || !check_date($date) || $time == ""){
			return false;
		}
		$service = new Service_Booking();
		$sql = "SELECT SUM(TIME) TOTAL_TIME FROM SERVICE_BOOKING WHERE ID IN (".$listService.")";
		$service->query($sql);
		$service->fetch();
		$total_time = $service->TOTAL_TIME;
		$date = date_to_date_sql($date);
        $hours = Booking::getHour($time);
		$start_date = $date. " {$hours}:00";
        
		$end_date = date("Y-m-d H:i:s", strtotime($start_date) + ($total_time*60) );
        
		$sql = "SELECT * FROM `TECHNICIAN` TC 
				LEFT JOIN (
				SELECT BDS.ID_BOOKING_DETAIL, BD.ID_TECHNICIAN, BD.BOOKING_DATE S_DATE, (
				BD.BOOKING_DATE + INTERVAL SUM( SB.`TIME` )
				MINUTE
				)E_DATE
				FROM BOOKING B
				INNER JOIN BOOKING_DETAIL BD ON BD.ID_BOOKING = B.ID
				INNER JOIN BOOKING_DETAIL_SERVICE BDS ON BDS.ID_BOOKING_DETAIL = BD.ID
				INNER JOIN SERVICE_BOOKING SB ON SB.ID = BDS.ID_SERVICE_BOOKING
				WHERE DATE(BD.BOOKING_DATE) = '{$date}'
				GROUP BY BDS.ID_BOOKING_DETAIL
				) BOOKED ON BOOKED.ID_TECHNICIAN = TC.ID
				WHERE TC.DISABLED = 0 AND (
					(S_DATE IS NULL AND E_DATE IS NULL) OR
					(S_DATE > '".$end_date."' OR E_DATE < '".$start_date."' ))";
		$tech = new Technician();
		$tech->query($sql);
		$str = "";
		if($tech->N >0){
			while($tech->fetch()){
				$str .= '<input type="radio" name="TECHNICIAN" id="TECHNICIAN" value="'.$tech->ID.'"/>'.$tech->TITLE.'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'; 
			}
		}
		return $str;
	}
	public static function ajaxTechnicianList($bdID,$techID){
		$sql = "SELECT BD.BOOKING_DATE S_DATE, DATE_ADD(BOOKING_DATE, INTERVAL SUM(S.TIME) MINUTE ) E_DATE FROM BOOKING_DETAIL BD
				INNER JOIN BOOKING_DETAIL_SERVICE BDS ON BDS.ID_BOOKING_DETAIL = BD.ID
				INNER JOIN SERVICE_BOOKING S ON S.ID = BDS.ID_SERVICE_BOOKING
				WHERE BD.ID = '{$bdID}'
				GROUP BY BD.ID";
		$bookedPeriod = new Booking_detail();
		$bookedPeriod->query($sql);
		$bookedPeriod->fetch();
		$period = array("S_DATE"=>$bookedPeriod->S_DATE,"E_DATE"=>$bookedPeriod->E_DATE);
		$tech = new Technician();
		$tech->DISABLED = 0;
		$tech->find();
		$arrTech = array();
		while($tech->fetch()){
			$arrTech[$tech->ID] = array(
				"ID" =>$tech->ID,
				"TITLE" => $tech->TITLE,
				"AVAILABLE" => true
			);
		}
		$sql = "SELECT ID_TECHNICIAN,ID_BOOKING_DETAIL, BD.BOOKING_DATE S_DATE, SUM(S.TIME) TOTAL_TIME, DATE_ADD(BOOKING_DATE, INTERVAL SUM(S.TIME) MINUTE) E_DATE FROM BOOKING B
					INNER JOIN BOOKING_DETAIL BD ON BD.ID_BOOKING = B.ID
					INNER JOIN BOOKING_DETAIL_SERVICE BDS ON BDS.ID_BOOKING_DETAIL = BD.ID
					INNER JOIN SERVICE_BOOKING S ON S.ID = BDS.ID_SERVICE_BOOKING
					WHERE DATE(BD.BOOKING_DATE) = DATE('{$period['E_DATE']}')
					GROUP BY BD.ID";
		$tech = new Technician();
		$tech->query($sql);
		while($tech->fetch()){
			if(($tech->S_DATE > $period['E_DATE'] || $tech->E_DATE > $period['S_DATE'] ) && $tech->ID_BOOKING_DETAIL != $bdID ){
				$arrTech[$tech->ID_TECHNICIAN]['AVAILABLE'] = false;
			}
		}
		$text = '<select id="technician_'.$bdID.'" name="technician_'.$bdID.'">';
		$text .= '<option value="0">'._LB_NONE.'</option>';
		foreach($arrTech as $tech){
			if(!$tech['AVAILABLE']){
				continue;
			}
			$text .= '<option value="'.$tech['ID'].'" '.($tech['ID'] == $techID?"selected='selected'":"").'>'.$tech['TITLE'].'</option>'; 
		}
		$text .= '</selected>';
		return $text;
	}
	public static function checkAvailableTech($bdID,$techID){
		$sql = "SELECT BD.BOOKING_DATE S_DATE, DATE_ADD(BOOKING_DATE, INTERVAL SUM(S.TIME) MINUTE ) E_DATE FROM BOOKING_DETAIL BD
				INNER JOIN BOOKING_DETAIL_SERVICE BDS ON BDS.ID_BOOKING_DETAIL = BD.ID
				INNER JOIN SERVICE_BOOKING S ON S.ID = BDS.ID_SERVICE_BOOKING
				WHERE BD.ID = '{$bdID}'
				GROUP BY BD.ID";
		$bookedPeriod = new Booking_detail();
		$bookedPeriod->query($sql);
		$bookedPeriod->fetch();
		$period = array("S_DATE"=>$bookedPeriod->S_DATE,"E_DATE"=>$bookedPeriod->E_DATE);
		
		$sql = "SELECT
					ID_TECHNICIAN,
					ID_BOOKING_DETAIL,
					BD.BOOKING_DATE      S_DATE,
					SUM(S.TIME)          TOTAL_TIME,
					DATE_ADD(BOOKING_DATE, INTERVAL SUM(S.TIME) MINUTE)    E_DATE
				  FROM BOOKING B
					INNER JOIN BOOKING_DETAIL BD
				  ON BD.ID_BOOKING = B.ID
					INNER JOIN BOOKING_DETAIL_SERVICE BDS
				  ON BDS.ID_BOOKING_DETAIL = BD.ID
					INNER JOIN SERVICE_BOOKING S
				  ON S.ID = BDS.ID_SERVICE_BOOKING
				  WHERE DATE(BD.BOOKING_DATE) = DATE('{$period['E_DATE']}') && ID_TECHNICIAN = '{$techID}' AND BD.ID != '{$bdID}'
				  GROUP BY BD.ID
				  HAVING !(S_DATE > '{$period['E_DATE']}' || E_DATE < '{$period['S_DATE']}' )";
		$tech = new Technician();
		$tech->query($sql);
		if($tech->N == 0){
			return true;
		}
		else{
			return false;
		}
	}
	public static function updateTechnicianBooking($bdID,$techID){
		if(!Technician::checkAvailableTech($bdID,$techID)){
			return false;
		}
		
		$booking = new Booking_detail();
		$booking->ID = $bdID;
		$booking->ID_TECHNICIAN = $techID;
		$booking->update();
		return "OK";
	}
}
?>
